/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ca.trader.db;

import ca.trader.except.UserAlreadyExistsException;
import java.sql.*;
import ca.trader.user.User;

/**
 *
 * @author Ahsan
 */
public class DBDriver
{
	 /*
	  * definitions for the database* 
	  */

	 private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	 private static final String DB_USERNAME = "root";
	 private static final String DB_PASSWORD = "";
	 private static final String DB_HOST = "localhost";
	 private static final String DB_PORT = "3306";
	 private static final String DB_NAME = "trader";
	 private static Connection dbCon;
	 private static final String DB_URL = String.format("jdbc:mysql://%s:%s/%s", DB_HOST, DB_PORT, DB_NAME);

	 private static Connection connect() throws ClassNotFoundException, SQLException
	 {
		  if (dbCon == null)
		  {
			   Class.forName(DB_DRIVER);
			   dbCon = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		  }
		  return dbCon;
	 }

	 public static ResultSet executeStatement(String statement) throws ClassNotFoundException, SQLException
	 {
		  Connection connection = connect();

		  Statement sqlStatementImp = connection.createStatement();

		  return sqlStatementImp.executeQuery(statement);
	 }

	 public static int executeUpdate(String statement) throws ClassNotFoundException, SQLException
	 {
		  Connection connection = connect();

		  Statement sqlStatementImp = connection.createStatement();

		  return sqlStatementImp.executeUpdate(statement);
	 }

	 public static void registerUser(User user, String cryptedpassword) throws ClassNotFoundException, SQLException, UserAlreadyExistsException
	 {
		  if (getUserFromDB(user.getEmail()) != null)
		  {
			   throw new UserAlreadyExistsException(user.getEmail());
		  }
		  
		  String statement = String.format("INSERT INTO %s (%s, %s, %s, %s) VALUES ('%s', '%s', '%s', '%s')",
				UserTable.NAME, UserTable.COL_EMAIL, UserTable.COL_FIRSTNAME, UserTable.COL_LASTNAME, UserTable.COL_PASSWORD,
				user.getEmail(), user.getFirstName(), user.getLastName(), cryptedpassword);

		  executeUpdate(statement);
	 }

	 public static User getUserFromDB(String email) throws ClassNotFoundException, SQLException
	 {
		  return getUserFromDB(email, null);
	 }

	 public static User getUserFromDB(String email, StringBuffer userDBCryptedPassword) throws ClassNotFoundException, SQLException
	 {
		  String sqlStatement = String.format("SELECT %s, %s, %s, %s FROM %s WHERE %s = '%s'",
				UserTable.COL_ID, UserTable.COL_FIRSTNAME, UserTable.COL_LASTNAME, UserTable.COL_PASSWORD,
				UserTable.NAME, UserTable.COL_EMAIL, email);

		  ResultSet resultSet = executeStatement(sqlStatement);


		  if (resultSet.next())
		  {
			   User user = new User();
			   user.setEmail(email);
			   user.setId(resultSet.getInt(1));
			   user.setFirstName(resultSet.getString(2));
			   user.setLastName(resultSet.getString(3));

			   if (userDBCryptedPassword != null)
			   {
				    userDBCryptedPassword.delete(0, userDBCryptedPassword.length());
				    userDBCryptedPassword.append(resultSet.getString(4));
			   }

			   return user;
		  }

		  return null;
	 }

}
